/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBlayer;

import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author bruhn
 */
public class DBSupplier implements IFDBSupplier {
    private Connection con;

    public DBSupplier()
    {
      con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList<Supplier> getAllSupplier()
    {
        return miscWhere("");
    }

    public Supplier findSupplier(int SupplierID)
    {
        String wClause = "  supplierID = '" + SupplierID + "'";
        return singleWhere(wClause);
    }

    public Supplier searchSupplier(String name, boolean retriveAssociation) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int insert(Supplier sup) {

       int rc = -1;
	   String query="INSERT INTO supplier(name, address, country, phoneno, email)  VALUES('"+
                        sup.getName() + "','" +
                        sup.getAddress() + "','" +
                        sup.getCountry() + "','" +
                        sup.getPhoneno() + "','" +
			sup.getEmail() + ")'";

       System.out.println("insert : " + query);
       try{ // insert new employee +  dependent
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("Supplier ikke oprettet");
          System.out.println("Insert exception in persiSupplier db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
       return(rc);
    
    }

    public int update(Supplier sup) {
        Supplier supplierObj  = sup;
		int rc=-1;

		String query="UPDATE persiSupplier SET "+
                          "name ='" + supplierObj.getName() +", " +
                          "address ='" + supplierObj.getAddress() +", " +
                          "country ='" + supplierObj.getCountry() +", " +
                          "phoneno ='" + supplierObj.getPhoneno() +", " +
			  "email ='" + supplierObj.getEmail();


                System.out.println("Update query:" + query);
  		try{ // update salesOrder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in persiSupplier db: "+ex);
	  	}
		return(rc);
    }

    public int delete(Supplier sup)
    {
        int rc=-1;

	  	String query="DELETE FROM persiSupplier WHERE supplierID = '" +
				sup + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in persiSupplier db: "+ex);
   	        }
		return(rc);
    }

    private ArrayList<Supplier> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();

	    String query =  buildQuery(wClause);

            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);


		while( results.next() ){
	     	 Supplier supplierObj = new Supplier();
		 supplierObj = buildSupplier(results);
                 list.add(supplierObj);
		}//end while
                 stmt.close();


		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one employee
	private Supplier singleWhere(String wClause)
	{
		ResultSet results;
		Supplier supplierObj = new Supplier();

	        String query = buildQuery(wClause);
                System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            supplierObj = buildSupplier(results);
                            //assocaition is to be build
                            stmt.close();

			}
                        else{ //no employee was found
                            supplierObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return supplierObj;
	}
	//method to build an employee object
        
        private String buildQuery(String wClause)
	{
	    String query="SELECT supplierID, name, address, country, phoneno, email FROM persiSupplier";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Supplier buildSupplier(ResultSet results)
        {
            Supplier supplierObj = new Supplier();
             try{ // the columns from the table emplayee  are used
                     supplierObj.setName(results.getString("name"));
                     supplierObj.setAddress(results.getString("address"));
                     supplierObj.setCountry(results.getString("country"));
                     supplierObj.setPhoneno(results.getInt("phoneno"));
                     supplierObj.setEmail(results.getString("email"));
                     supplierObj.setSupplierID(results.getInt("supplierID"));

                 }
                 catch(Exception e)
                 {
                     System.out.println("error in building the supplier object");
                 }
          return supplierObj;
        }

}
